package dbclass;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import classes.DbClass;

import beans.Tag;

public class TagJDBC implements TagDAO{	

	private Connection con;
	
	
	public Tag insert(Tag tag) throws SQLException, ClassNotFoundException
	{
		con = DbClass.getConnection();
		Statement stm = con.createStatement();
		tag.setNome(tag.getNome().trim());
		boolean check = false;
		
		if(tag.getNome() != "" && tag.getNome() != null) {
			 check =  stm.execute("INSERT INTO tag (nome, id_noticia) VALUES ('" + tag.getNome().toLowerCase() + "', '" + tag.getId_noticia()+ "')");
		}

		if(check)
		{
			return tag;
		}	
		return null;	
	}
	
	public Tag update(Tag tag) throws SQLException, ClassNotFoundException
	{
		con = DbClass.getConnection();
		Statement stm = con.createStatement();
		
		boolean check = stm.execute("UPDATE tag SET id_noticia = '" + tag.getId_noticia() + "' WHERE nome = '" + tag.getNome() + "'");
		
		if(check)
		{
			return tag;
		}	
		return null;
	}

	public boolean delete(String nome) throws ClassNotFoundException, SQLException {		
		con = DbClass.getConnection();
		Statement stm = con.createStatement();
		int check = stm.executeUpdate("DELETE FROM tag WHERE nome = '" + nome + "' LIMIT 1");
		if(check > 0)
			return true;
		return false;
	}

	public boolean deleteAll(Long id) throws ClassNotFoundException, SQLException {
		con = DbClass.getConnection();
		Statement stm = con.createStatement();
		boolean check = stm.execute("DELETE FROM tag WHERE id_noticia = '" + id + "' ");
		return check; 
	}
	public List<Tag> findAll() throws SQLException, ClassNotFoundException {
		List<Tag> tags = new ArrayList<Tag>();
		
		con = DbClass.getConnection();
		Statement stm = con.createStatement();
		
		ResultSet result;
		result = stm.executeQuery("SELECT * FROM tag");
		
		while (result.next()) {
			Tag tag = new Tag(result.getString(1), result.getLong(2));
            tags.add(tag);
        }		
		return tags;
	}


	public Tag findById(String nome) throws SQLException, ClassNotFoundException {
		Tag tag = null;
		
		con = DbClass.getConnection();
		Statement stm = con.createStatement();
		
		ResultSet result;
		result = stm.executeQuery("SELECT * FROM tag WHERE nome = '" + nome + "'");
		
		while (result.next()) {
			tag = new Tag(result.getString(1), result.getLong(2));
        }		
		return tag;
	}
	
	//Encontra todas as tags de uma noticia
	public String findByIdNoticia(long id_noticia) throws SQLException, ClassNotFoundException {
		String tag = "";
		
		con = DbClass.getConnection();
		Statement stm = con.createStatement();
		
		ResultSet result;
		result = stm.executeQuery("SELECT nome FROM tag WHERE id_noticia = '" + id_noticia + "'");
		
		while (result.next()) {
			tag += result.getString(1) + ", ";  
        }		
		return tag;
	}
	//Retorna nuvem de tags: retorna a tag e quantas vezes ela aparece
	public ResultSet nuvem() throws SQLException, ClassNotFoundException {
		con = DbClass.getConnection();
		Statement stm = con.createStatement();
		 
		return stm.executeQuery("select count(*) as total, t.nome FROM tag t group by nome order by nome asc");
	}
	
	

}